PostgreSQLのUPSERT時にデッドタプルはどう増える?

#PostgreSQL

quiver

2022.12.07

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

本エントリーはPostgreSQL Advent Calendar 2022の7日目の記事です。

UPSERT時のデッドタプルについて簡単に動作確認してみたので、共有します。

前日の記事は @toshikawa さんによる次の記事でした。

UPSERT とは

データベースにレコードを反映する際、新規の場合は INSERT 、更新の場合は UPDATE というようにデータの状態によって UPDATE と INSERT を使い分けることを、UPDATE と INSERT をくっつけて UPSERT と呼びます。

PostgreSQL でUPSERT処理を記述する場合、INSERT ON CONFLICT構文が古くから利用されており、15からはMERGE文も利用できるようになりました。

PostgreSQL UPSERTの詳細については、明日9日目のアドカレ @meijik さんの『UPSERT大全』をお楽しみください！

以下では、UPSERT実行時にデッドタプルがどのように生成されるか確認します。

テスト用テーブル

テスト用のシンプルなテーブルを用意します。

CREATE TABLE blog (
  id int PRIMARY KEY, 
  name varchar(20)
);

シンプルな INSERT の場合

まっさらなテーブルに、新規レコードを追加します。

postgres=> INSERT INTO blog values(1, 'a');
INSERT 0 1

postgres=> SELECT relname, n_dead_tup, n_live_tup
FROM pg_stat_user_tables
WHERE relname = 'blog';

 relname | n_dead_tup | n_live_tup
---------+------------+------------
 blog    |          0 |          1
(1 row)

INSERTに対応するliveタプルが一つだけ存在します。

同じプライマリーキーで INSERT すると、duplicate key エラーが発生します。

INSERT INTO blog(id, name)
VALUES(1, 'a');

ERROR:  duplicate key value violates unique constraint "blog_pkey"
DETAIL:  Key (id)=(1) already exists.

INSERT に失敗したタプルがdeadとマークされるため、n_dead_tup が1つ増えます。

n_dead_tup	n_live_tup
1	1

INSERT ON CONFLICT UPSERTの場合

次に INSERT ON CONFLICT の UPSERT で UPDATE します。

INSERT INTO blog(id, name)
VALUES(1, 'b')
ON CONFLICT(id)
DO UPDATE SET name = excluded.name;

INSERT 0 1

UPDATEにより、初回INSERT時のタプルがデッドとマークされるため、n_dead_tup が1つ増えました。

n_dead_tup	n_live_tup
2	1

次に、少し変則的に INSERT コンフリクト時に UPDATE の代わりに何もしないことにします(DO NOTHING)。

INSERT INTO blog(id, name)
VALUES(1, 'c')
ON CONFLICT(id)
DO NOTHING;

INSERT 0 0

この場合、デッドタプルは増えていません。

n_dead_tup	n_live_tup
2	1

INSERT..ON CONFLICT 文には投機的挿入(speculative insertion)という仕組みがあり、INSERT前に制約違反がないか事前チェックし、違反があると判断した場合は INSERT の代わりに ON CONFLICT 文を実行するからです。

先のUPSERT において、デッドタプルが増えたのはUPDATE に対応する 1タプル分だけなのも、納得です。コンフリクトで失敗したINSERTと成功したUPDATEの2タプル分が増えるわけではありません。

MERGE UPSERTの場合

次に、PostgreSQL 15から対応した MERGEの動作を確認します。

MERGEでUPDATEを走らせます。

MERGE INTO blog AS t
USING (VALUES (1, 'd')) AS i(id, name)
ON t.id = i.id
WHEN MATCHED THEN
    UPDATE SET name = i.name
WHEN NOT MATCHED THEN
    INSERT (id, name) VALUES (i.id, i.name);

MERGE 1

UPDATEにより最新のタプルが置き換わったため、デッドタプルが1つ増えます。

n_dead_tup	n_live_tup
3	1

先程の ON CONFLICT DO NOTHING と同様に、マッチ時には何もしないようにします。

MERGE INTO blog AS t
USING (VALUES (1, 'e')) AS i(id, name)
ON t.id = i.id
WHEN MATCHED THEN
    DO NOTHING
WHEN NOT MATCHED THEN
    INSERT (id, name) VALUES (i.id, i.name);

MERGE 0

この場合、デッドタプルは増えていません。

n_dead_tup	n_live_tup
3	1

MERGEではソース・ターゲットテーブル間でJOINしてマッチ判定をしています。MATCHEDとNOT MATCHED(live)の二律背反です。

First, the MERGE command performs a join from data_source to target_table_name producing zero or more candidate change rows. For each candidate change row, the status of MATCHED or NOT MATCHED is set just once, after which WHEN clauses are evaluated in the order specified. For each candidate change row, the first clause to evaluate as true is executed. No more than one WHEN clause is executed for any candidate change row.

https://www.postgresql.org/docs/15/sql-merge.html

並列INSERTでコンフリクトした場合

最後に、INSERT..ON CONFLICT/MERGE を使って並列で同じキーの新規INSERTが走り、あと負けした場合を考えます。

MERGE の場合の具体的な実行例は以下の通りです。

TX A	TX B
BEGIN
	BEGIN
`INSERT INTO blog(id, name)` `VALUES(3, 'a');`
行排他ロック獲得
	`MERGE INTO INTO blog AS t` `USING (VALUES (3, 'test')) AS i(id, name)` `ON t.id = i.id` `WHEN MATCHED THEN` `UPDATE SET name = i.name` `WHEN NOT MATCHED THEN` `INSERT (id, name) VALUES (i.id, i.name);`
	共有ロックの granted : false TX Aの終了待ち
COMMIT
	ERROR: duplicate key value violates unique constraint "blog_pkey" DETAIL: Key (id)=(3) already exists.

※ INSERT..ON CONFLICTの場合は、メインのSQLを読み替えてください。

結果として、投機的挿入を使う INSERT..ON CONFLICT はUPDATEとして成功し、MERGEは上記のように制約違反により INSERTが失敗します。

When MERGE is run concurrently with other commands that modify the target table, the usual transaction isolation rules apply; see Section 13.2 for an explanation on the behavior at each isolation level. You may also wish to consider using INSERT ... ON CONFLICT as an alternative statement which offers the ability to run an UPDATE if a concurrent INSERT occurs. There are a variety of differences and restrictions between the two statement types and they are not interchangeable.

https://www.postgresql.org/docs/15/sql-merge.html

タプルを確認すると、いずれのケースも liveとdeadのタプルが1つずつ増えます。